package com.lecing.utils;

import org.apache.poi.hssf.converter.ExcelToHtmlConverter;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 将Excel解析为对象
 * Created by liucs on 2018/5/25.
 */
public class ExcelHelper {
    /**
     * 将excel解析为指定的对象集合 <br>
     * 例如： 要导入的excel格式为 <br>
     * 第1行：  | id   | username | password　|(与对象的字段对应)<br>
     * 第2行：  |1     | 小周　　 | 123456    |<br>
     * 第3行：  |2     | 老王　　 | 123456    |<br>
     * 调用： analysisExcel(file,User.class);
     * @param file-----要解析的excel文件
     * @param c--------指定的对象类型
     * @throws IOException
     * @return---------对象集合
     */
    public static <T> List<T> analysisExcel(File file, Class<T> c) {
        List<T> list = new ArrayList<T>();
        InputStream inputStream = null;
        String fileName = null;
        Workbook wb = null;
        try{
            inputStream = new FileInputStream(file);
            fileName = file.getName();
            if(fileName.endsWith(".xls") || fileName.endsWith(".xlsx")){
                //如果是2003版本
                if(fileName.endsWith(".xls")){
                    //1.先解析文件
                    POIFSFileSystem fs = new POIFSFileSystem(inputStream);
                    wb = new HSSFWorkbook(fs);
                }else if( fileName.endsWith(".xlsx")){//如果是2007以上版本
                    wb = new XSSFWorkbook(inputStream);
                }else{
                    return null;
                }
            }
        }catch(IOException e){
            e.printStackTrace();
        }
        Sheet sheet = wb.getSheetAt(0);
        //获取第一行（标题行）
        Row row1 = sheet.getRow(0);
        //总列数
        int colNum = row1.getPhysicalNumberOfCells();
        //总行数
        int rowNum = sheet.getLastRowNum();
        //将标题行一一放入数组
        String[] titles = new String[colNum];
        for(int i = 0 ; i < colNum ; i++){
            titles[i] = row1.getCell(i).getStringCellValue();
        }
        //获取指定对象所有的字段
        Field fields[] = c.getDeclaredFields();
        Map<String,Field> fieldMap = new HashMap<String, Field>();
        for (int i = 0; i < fields.length; i++) {
            fieldMap.put(fields[i].getName(), fields[i]);
        }
        //使用反射机制，将值存入对应对象中
        try {
            for (int i = 1; i < rowNum+1; i++) {
                T t =c.newInstance();
                for (int j = 0; j < titles.length; j++) {
                    //当excel中有这个字段
                    if(fieldMap.containsKey(titles[j])){
                        String fieldName = titles[j];
                        String methodName = "set" + fieldName.substring(0,1).toUpperCase()+fieldName.substring(1);
                        //调用该字段对应的set方法
                        Class cc = fieldMap.get(titles[j]).getType();
                        Method method = c.getMethod(methodName, cc);
                        String value = String.valueOf(sheet.getRow(i).getCell(j));
                        method.invoke(t, parseValue(value, cc));
                    }
                }
                list.add(t);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 将字符串转化为指定类型的对象
     * @param s----要转化的字符串
     * @param c----目标对象类型
     * @return
     */
    private static Object parseValue(String s,Class c){
        Object obj = null;
        String className = c.getName();
        //excel中的数字解析之后可能末尾会有.0，需要去除
        if(s.endsWith(".0")) s = s.substring(0, s.length()-2);

        if(className.equals("java.lang.Integer")){ //Integer
            obj =  new Integer(s);
        }else if(className.equals("int")){ //int
            obj = (int)Integer.parseInt(s);
        }else if(className.equals("java.lang.String")){ //String
            obj = s;
        }else if(className.equals("java.lang.Double")){ //Double
            obj = new Double(s);
        }else if(className.equals("double")){ //double
            obj = (double)new Double(s);
        }else if(className.equals("java.lang.Float")){ //Float
            obj = new Float(s);
        }else if(className.equals("float")){ //float
            obj = (float)new Float(s);
        }else if(className.equals("java.util.Date")){ //Date
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            try {
                obj = sdf.parse(s);
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }else if(className.equals("long")){ //long
            obj = Long.parseLong(s);
        }else if(className.equals("java.util.Long")){ //Long
            obj = new Long(s);
        }
        return obj;
    }
    /**
     * 将2003版Excel文件转为html标签元素
     * @param path
     * @return
     * @throws IOException
     * @throws ParserConfigurationException
     * @throws TransformerException
     * @throws InvalidFormatException
     */
    public static  String convertExcel2Html(String path) throws IOException,ParserConfigurationException, TransformerException,InvalidFormatException {
        HSSFWorkbook workBook = null;
        String content = null;
        StringWriter writer = null;
        File excelFile = new File(path);
        InputStream is = new FileInputStream(excelFile);
        Sheet sheet=null;
        //判断Excel文件是2003版还是2007版
        String suffix = path.substring(path.lastIndexOf("."));
        if(suffix.equals(".xlsx")){
            //将07版转化为03版
            //Xssf2Hssf xlsx2xls = new Xssf2Hssf();
            XSSFWorkbook xSSFWorkbook = new XSSFWorkbook(is);
            // workBook = new HSSFWorkbook();
            // xlsx2xls.transformXSSF(xSSFWorkbook, workBook);
        }else{
            workBook = new HSSFWorkbook(is);
            workBook.setActiveSheet(0);
        }
        try {
           // workBook.setSheetVisibility(workBook.getActiveSheetIndex(), SheetVisibility.HIDDEN);
            ExcelToHtmlConverter converter = new ExcelToHtmlConverter(DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument());
//            ExcelToHtmlConverter e2hconverter = new ExcelToHtmlConverter();
//            converter.setOutputHiddenColumns(true);
//            converter.setOutputHiddenRows(true);
            converter.setOutputColumnHeaders(false);// 不显示列的表头
            converter.setOutputRowNumbers(false);// 不显示行的表头

            converter.processWorkbook(workBook);


            writer = new StringWriter();
            Transformer serializer = TransformerFactory.newInstance().newTransformer();
            serializer.setOutputProperty(OutputKeys.ENCODING, "UTF-8");
            serializer.setOutputProperty(OutputKeys.INDENT, "yes");
            serializer.setOutputProperty(OutputKeys.STANDALONE,"standlone");
            serializer.setOutputProperty(OutputKeys.METHOD, "html");
            DOMSource domSource=new DOMSource(converter.getDocument());
            String fstNodeName=domSource.getNode().getFirstChild().getNodeName();
            System.out.println("firstNodeName:"+fstNodeName);

            serializer.transform(new DOMSource(converter.getDocument()), new StreamResult(writer));

            content = writer.toString();
            content=content.replace(workBook.getSheetName(0),"");
            writer.close();

            //output the html file
        } finally {
            try {
                if (is != null) {
                    is.close();
                }
                if (writer != null) {
                    writer.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return content;
    }
    /**
     * 测试
     * @param args
     */
    public  static void main(String[] args) throws Exception{
        //file:要解析的excel文件
        // excel中第一行要和你要解析的目标对象字段名称一致。
        /**
        File file=new File("e:/mRepayExcl05.xlsx");
        List<MPayRefund> mPayRefunds = ExcelHelper.analysisExcel(file, MPayRefund.class);
        for (MPayRefund payRefund:mPayRefunds) {
            System.out.println(payRefund);
        }
         */
        String fpath="e:/mRepayExcl05.xls";
        String excHtml=convertExcel2Html(fpath);
        System.out.println("excel转换为html结果："+excHtml);
    }
}
